package com.alex.mda.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import com.alex.mda.IConstants;
import com.alex.mda.jdbc.meta.Catalog;
import com.alex.mda.jdbc.meta.Schema;
import com.alex.mda.jdbc.meta.Table;

public class MetaService {

	private static MetaService me = new MetaService();

	public static MetaService getInstance() {
		return me;
	}

	private Map<String, Connection> conMap = Collections
			.synchronizedMap(new LinkedHashMap<String, Connection>());

	private Map<String, String> urlMap = Collections
			.synchronizedMap(new LinkedHashMap<String, String>());

	private Map<String, Connection> getConnMap() throws IOException {
		if (conMap.size() == 0)
			getConnections();
		return conMap;
	}

	public Set<String> getConnNames() throws IOException {
		Map<String, Connection> connMap = getConnMap();
		Set<String> connNameSet = connMap.keySet();
		return connNameSet;
	}

	public Connection getConn(String connName) throws IOException, SQLException {
		Map<String, Connection> connMap = getConnMap();
		Connection conn = connMap.get(connName);
		// System.out.println(conn.isValid(10));
		if (conn == null) {
			getConnections();
		} else if (conn.isClosed()) {
			conn = DriverManager.getConnection(urlMap.get(connName));
			conMap.put(connName, conn);
		}
		return conn;
	}

	public void releaseConnection() throws SQLException {
		Collection<Connection> conns = conMap.values();
		for (Iterator<Connection> iterator = conns.iterator(); iterator
				.hasNext();) {
			Connection conn = iterator.next();
			if (conn != null) {
				conn.close();
			}
		}
		conMap.clear();
		urlMap.clear();
	}

	public Map<String, Catalog> getCatalogs(Connection conn)
			throws SQLException {
		Map<String, Catalog> map = new LinkedHashMap<String, Catalog>();
		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet rs = dbMetaData.getCatalogs();
		while (rs.next()) {
			String catalogName = rs.getString("TABLE_CAT");
			map.put(catalogName, new Catalog(catalogName));
		}

		return map;
	}

	public String getDefaultCatalog(Connection conn) throws SQLException {
		return conn.getCatalog();
	}

	public Map<String, Schema> getSchemas(Connection conn, String catalogName)
			throws SQLException {
		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet rs = dbMetaData.getSchemas(catalogName, "%");
		Map<String, Schema> map = new LinkedHashMap<String, Schema>();
		while (rs.next()) {
			String schemaName = rs.getString("TABLE_SCHEM");
			map.put(schemaName, new Schema(schemaName, catalogName));
		}

		return map;
	}

	// for mysql ...
	public Map<String, Table> getTables(Connection conn, String catalogName)
			throws SQLException {
		return getTables(conn, catalogName, "%");
	}

	// for oracle ...
	public Map<String, Table> getTables(Connection conn, String catalogName,
			String schemaName) throws SQLException {
		ResultSet rs = conn.getMetaData().getTables(catalogName, schemaName,
				"%", new String[] { "TABLE" });
		Map<String, Table> tables = new LinkedHashMap<String, Table>();
		while (rs.next()) {
			String tableName = rs.getString("TABLE_NAME");
			Table table = new Table(tableName, catalogName, schemaName);
			table.setRemarks(rs.getString("REMARKS"));
			tables.put(tableName, table);
		}
		return tables;
	}

	private List<String> getConnections() throws IOException {
		InputStream input = MetaService.class
				.getResourceAsStream(IConstants.MDA_DATASOURCE);
		Properties props = new Properties();
		props.load(input);
		List<String> nameList = new ArrayList<String>();

		for (int i = 1; i <= 10; i++) {
			String name = (String) props.get("name" + i);
			if (name == null) {
				continue;
			}

			try {
				Class.forName((String) props.get("driver" + i));
				Class.forName((String) props.get("driver" + i)).newInstance();
				String url = String.format((String) props.get("url" + i),
						(String) props.get("username" + i),
						(String) props.get("password" + i));
				Connection conn = DriverManager.getConnection(url);
				conMap.put(name, conn);
				urlMap.put(name, url);
				nameList.add(name);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return nameList;
	}

	public static void main(String[] args) throws IOException {
	}
}
